<?php
/**
 * Created by PhpStorm.
 * User: admin
 * Date: 2019/5/29
 * Time: 23:52
 */

namespace app\storage\service;


use app\storage\model\MaterielModel;
use app\storage\model\ReelsModel;
use service\excel\ExcelWrite;
use think\Db;

class QueryService
{
    use \app\storage\util\Common;
    public function downLoadMateriel($where)
    {
        $havingArr = ['reel_count', 'type_count', 'storage_count'];
        $having = $this->getHavingString($havingArr, $where);

        $map = [];
        if (!empty($where['sub_materiel_name'])) {
            $map['sub_materiel_name'] = $where['sub_materiel_name'];
            unset($where['sub_materiel_name']);
        }
        if (!empty($where['shelves_type'])) {
            $map['shelves_type'] = $where['shelves_type'];
            unset($where['shelves_type']);
        }

        if (!empty($where['sub_materiel_id'])) {
            $map['m.sub_materiel_id'] = $where['sub_materiel_id'];
            unset($where['sub_materiel_id']);
        }

        if (!empty($where['min_quantity'])) {
            $map['m.min_quantity'] = $where['min_quantity'];
            unset($where['min_quantity']);
        }

        if (!empty($where['item_id'])) {
            $map['m.item_id'] = $where['item_id'];
            unset($where['item_id']);
        }

        $supplierCount = $where['supplier_count'][1];
        $sType = 'left';
        if (!empty($supplierCount)) {
            $supplierCount = "count like '$supplierCount'" ;
            $sType = 'right';
            unset($where['supplier_count']);
        }

        $storageUuid = $where['storage_uuid'];
        $type = empty($storageUuid) ? 'left' : 'right';

        $where['status'] = 1;
        $reelsModel = new ReelsModel();
        $baseSql = $reelsModel->alias('r')
            ->join(['iwh_storage' => 's'], 's.uuid = r.storage_uuid')
            ->field("sub_materiel_id,count(*) as type_count,sum(count) as reel_count")
            ->group('r.sub_materiel_id')
            ->having($having)
            ->where($where)
            ->select(false);

        if ($storageUuid) {
            $supplierSql = Db::table('iwh_supplier')->alias('s')->field('sub_materiel_id,count')
                ->join(['iwh_supplier_materiel' => 'sm'], 'sm.supplier_id = s.supplier_id')
                ->join(['iwh_supplier_storage' => 't'], 't.supplier_uuid = s.uuid')
                ->where(['storage_uuid' => $storageUuid])
                ->where($supplierCount)
                ->select(false);
        } else {
            $supplierSql = Db::table('iwh_supplier_materiel')->field('sub_materiel_id,count')->where($supplierCount)->select(false);
        }

        $subMaterielModel = new MaterielModel();

        $ret = $subMaterielModel->alias('m')
            ->join(["($baseSql)" => 'b'], 'b.sub_materiel_id = m.sub_materiel_id', $type)
            ->join(["($supplierSql)" => 'c'], 'c.sub_materiel_id = m.sub_materiel_id', $sType)
            ->field("shelves_type,m.sub_materiel_id,min_quantity,IFNULL(b.type_count,0) as type_count,IFNULL(b.reel_count,0) as reel_count,m.sub_materiel_name,item_id,IFNULL(c.count,0) as supplier_count")
            ->group('m.sub_materiel_id')
            ->having($having)
            ->where($map)
            ->select();

        $init = [
            'sub_materiel_id'=>'料号',
            'sub_materiel_name'=>'料名',
            'reel_count'=>'架上数量',
            'supplier_count'=>'账上数量',
            'type_count'=>'件数',
            'item_id'=>'内码',
            'min_quantity'=>'最小包装数量'
        ];
        $ret = count($ret)?$ret->toArray():[];
        ExcelWrite::excelExport('物料信息',$init,$ret);
    }

}